﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OA.UserManager
{
    /// <summary>
    /// 用户
    /// </summary>
    public class User
    {
        #region 属性
        public int Id { get; set; }
        public string Name { get; set; }
        public short Gender { get; set; }
        public string TrueName { get; set; }
        public string Password { get; set; }
        public DateTime Birthday { get; set; }
        public bool IsLockedOut { get; set; }
        public bool IsActived { get; set; }
        public DateTime CreateOn { get; set; }
        public DateTime LastLoginOn { get; set; }
        public string CreateBy { get; set; }
        #endregion

        #region 方法
        /// <summary>
        /// 获取用户实体对象
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static User GetUserById(int userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,CreateBy,LastLoginOn from Users ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = userId;

            User model = new User();
            DataSet ds = DbHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                model.TrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                if (ds.Tables[0].Rows[0]["Gender"].ToString() != "")
                {
                    model.Gender = short.Parse(ds.Tables[0].Rows[0]["Gender"].ToString());
                }
                model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                if (ds.Tables[0].Rows[0]["Birthday"].ToString() != "")
                {
                    model.Birthday = DateTime.Parse(ds.Tables[0].Rows[0]["Birthday"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsLockedOut"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsLockedOut"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsLockedOut"].ToString().ToLower() == "true"))
                    {
                        model.IsLockedOut = true;
                    }
                    else
                    {
                        model.IsLockedOut = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["IsActived"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsActived"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActived"].ToString().ToLower() == "true"))
                    {
                        model.IsActived = true;
                    }
                    else
                    {
                        model.IsActived = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["CreateOn"].ToString() != "")
                {
                    model.CreateOn = DateTime.Parse(ds.Tables[0].Rows[0]["CreateOn"].ToString());
                }
                model.CreateBy = ds.Tables[0].Rows[0]["CreateBy"].ToString();
                if (ds.Tables[0].Rows[0]["LastLoginOn"].ToString() != "")
                {
                    model.LastLoginOn = DateTime.Parse(ds.Tables[0].Rows[0]["LastLoginOn"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }

        }

        public static QueryResult<Feature> GetFeaturesByUser(int userId)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 添加角色关联用户
        /// </summary>
        /// <param name="role">角色</param>
        /// <param name="user">用户</param>
        /// <param name="createBy">操作人员</param>
        /// <returns></returns>
        public static int AddUserToRole(Role role, User user,int createBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into UsersInRole(");
            strSql.Append("UserId,RoleId,CreateOn,CreateBy)");
            strSql.Append(" values (");
            strSql.Append("@uerId,@roleId,@createOn,@createBy)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@userId", SqlDbType.Int,4),
					new SqlParameter("@roleId", SqlDbType.Int,4),
					new SqlParameter("@createOn", SqlDbType.DateTime,8),
					new SqlParameter("@createBy", SqlDbType.Int,4)};
            parameters[0].Value = user.Id;
            parameters[1].Value = role.Id;
            parameters[2].Value = DateTime.Now;
            parameters[3].Value = createBy;
            object obj = DbHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">用户信息</param>
        /// <returns></returns>
        public static bool Update(User user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Users set ");
            strSql.Append("Name=@Name,");
            strSql.Append("TrueName=@TrueName,");
            strSql.Append("Gender=@Gender,");
            strSql.Append("Password=@Password,");
            strSql.Append("Birthday=@Birthday,");
            strSql.Append("IsLockedOut=@IsLockedOut,");
            strSql.Append("IsActived=@IsActived,");
            strSql.Append("CreateOn=@CreateOn,");
            strSql.Append("CreateBy=@CreateBy,");
            strSql.Append("LastLoginOn=@LastLoginOn");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,20),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,6),
					new SqlParameter("@Gender", SqlDbType.SmallInt,2),
					new SqlParameter("@Password", SqlDbType.NVarChar,255),
					new SqlParameter("@Birthday", SqlDbType.Date,3),
					new SqlParameter("@IsLockedOut", SqlDbType.Bit,1),
					new SqlParameter("@IsActived", SqlDbType.Bit,1),
					new SqlParameter("@CreateOn", SqlDbType.DateTime),
					new SqlParameter("@CreateBy", SqlDbType.NVarChar,6),
					new SqlParameter("@LastLoginOn", SqlDbType.DateTime)};
            parameters[0].Value = user.Id;
            parameters[1].Value = user.Name;
            parameters[2].Value = user.TrueName;
            parameters[3].Value = user.Gender;
            parameters[4].Value = user.Password;
            parameters[5].Value = user.Birthday;
            parameters[6].Value = user.IsLockedOut;
            parameters[7].Value = user.IsActived;
            parameters[8].Value = user.CreateOn;
            parameters[9].Value = user.CreateBy;
            parameters[10].Value = user.LastLoginOn;

            int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 激活用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static bool ActiveUser(User user)
        {
            user.IsActived = true;
            return Update(user);
        }
        /// <summary>
        /// 创建用户
        /// </summary>
        /// <param name="user">用户信息</param>
        /// <returns></returns>
        public static int CreateUser(User user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Users(");
            strSql.Append("Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,CreateBy,LastLoginOn)");
            strSql.Append(" values (");
            strSql.Append("@Name,@TrueName,@Gender,@Password,@Birthday,@IsLockedOut,@IsActived,@CreateOn,@CreateBy,@LastLoginOn)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.NVarChar,20),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,6),
					new SqlParameter("@Gender", SqlDbType.SmallInt,2),
					new SqlParameter("@Password", SqlDbType.NVarChar,255),
					new SqlParameter("@Birthday", SqlDbType.Date,3),
					new SqlParameter("@IsLockedOut", SqlDbType.Bit,1),
					new SqlParameter("@IsActived", SqlDbType.Bit,1),
					new SqlParameter("@CreateOn", SqlDbType.DateTime),
					new SqlParameter("@CreateBy", SqlDbType.NVarChar,6),
					new SqlParameter("@LastLoginOn", SqlDbType.DateTime)};
            parameters[0].Value = user.Name;
            parameters[1].Value = user.TrueName;
            parameters[2].Value = user.Gender;
            parameters[3].Value = user.Password;
            parameters[4].Value = user.Birthday;
            parameters[5].Value = user.IsLockedOut;
            parameters[6].Value = user.IsActived;
            parameters[7].Value = user.CreateOn;
            parameters[8].Value = user.CreateBy;
            parameters[9].Value = user.LastLoginOn;

            object obj = DbHelper.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取所有用户信息
        /// </summary>
        /// <param name="page">当前页码</param>
        /// <param name="size">每页记录数</param>
        /// <returns></returns>
        public static QueryResult<User> GetAllUser(int page, int size)
        {
            QueryResult<User> result = new QueryResult<User>(page, size);
            #region 设置存储过程参数
            SqlParameter[] parameters = {
               new SqlParameter("@tblName",SqlDbType.NVarChar,200),
               new SqlParameter("@fldName",SqlDbType.NVarChar,500),
               new SqlParameter("@pageSize",SqlDbType.Int,4),
               new SqlParameter("@page",SqlDbType.Int,4),
               new SqlParameter("@pageCount",SqlDbType.Int,4),
               new SqlParameter("@Counts",SqlDbType.Int,4),
               new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
               new SqlParameter("@Sort",SqlDbType.Bit,1),
               new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
               new SqlParameter("@ID",SqlDbType.NVarChar,150),
               new SqlParameter("@Dist",SqlDbType.Bit,1)
            };
            parameters[0].Value = "Users";
            parameters[1].Value = "Id,Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,LastLoginOn";
            parameters[2].Value = size;
            parameters[3].Value = page;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            parameters[6].Value = "CreateOn";
            parameters[7].Value = true;
            parameters[8].Value = "";
            parameters[9].Value = "Id";
            parameters[10].Value = false;
            #endregion
            SqlDataReader reader = DbHelper.RunProcedure("proc_QueryPage", parameters);
            User user = new User();
            while (reader.Read())
            {
                user.Id = reader.GetInt32(0);
                user.Name = reader.GetString(1);
                user.TrueName = reader.GetString(2);
                user.Gender = reader.GetInt16(3);
                user.Password = reader.GetString(4);
                user.Birthday = reader.GetDateTime(5);
                user.IsLockedOut = reader.GetBoolean(6);
                user.IsActived = reader.GetBoolean(7);
                user.CreateOn = reader.GetDateTime(8);
                user.LastLoginOn = reader.GetDateTime(9);
                result.DataList.Add(user);
            }
            result.Count = int.Parse(parameters[5].Value.ToString());
            result.PageCount = int.Parse(parameters[4].Value.ToString());
            return result;
        }
        /// <summary>
        /// 查询激活/未激活用户
        /// </summary>
        /// <param name="isActive">激活/未激活</param>
        /// <param name="page">当前页码</param>
        /// <param name="size">每页记录数</param>
        /// <returns></returns>
        public static QueryResult<User> GetUsersByActive(bool isActive, int page, int size)
        {
            QueryResult<User> result = new QueryResult<User>(page, size);
            #region 设置存储过程参数
            SqlParameter[] parameters = {
               new SqlParameter("@tblName",SqlDbType.NVarChar,200),
               new SqlParameter("@fldName",SqlDbType.NVarChar,500),
               new SqlParameter("@pageSize",SqlDbType.Int,4),
               new SqlParameter("@page",SqlDbType.Int,4),
               new SqlParameter("@pageCount",SqlDbType.Int,4),
               new SqlParameter("@Counts",SqlDbType.Int,4),
               new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
               new SqlParameter("@Sort",SqlDbType.Bit,1),
               new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
               new SqlParameter("@ID",SqlDbType.NVarChar,150),
               new SqlParameter("@Dist",SqlDbType.Bit,1)
            };
            parameters[0].Value = "Users";
            parameters[1].Value = "Id,Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,LastLoginOn";
            parameters[2].Value = size;
            parameters[3].Value = page;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            parameters[6].Value = "CreateOn";
            parameters[7].Value = true;
            parameters[8].Value = "IsActived="+isActive;
            parameters[9].Value = "Id";
            parameters[10].Value = false;
            #endregion
            SqlDataReader reader = DbHelper.RunProcedure("proc_QueryPage", parameters);
            User user = new User();
            while (reader.Read())
            {
                user.Id = reader.GetInt32(0);
                user.Name = reader.GetString(1);
                user.TrueName = reader.GetString(2);
                user.Gender = reader.GetInt16(3);
                user.Password = reader.GetString(4);
                user.Birthday = reader.GetDateTime(5);
                user.IsLockedOut = reader.GetBoolean(6);
                user.IsActived = reader.GetBoolean(7);
                user.CreateOn = reader.GetDateTime(8);
                user.LastLoginOn = reader.GetDateTime(9);
                result.DataList.Add(user);
            }
            result.Count = int.Parse(parameters[5].Value.ToString());
            result.PageCount = int.Parse(parameters[4].Value.ToString());
            return result;
        }
        /// <summary>
        /// 查询锁定/未锁定用户
        /// </summary>
        /// <param name="isLockedOut">锁定/未锁定</param>
        /// <param name="page">当前页码</param>
        /// <param name="size">每页记录数</param>
        /// <returns></returns>
        public static QueryResult<User> GetUsersByLockedOut(bool isLockedOut, int page, int size)
        {
            QueryResult<User> result = new QueryResult<User>(page, size);
            #region 设置存储过程参数
            SqlParameter[] parameters = {
               new SqlParameter("@tblName",SqlDbType.NVarChar,200),
               new SqlParameter("@fldName",SqlDbType.NVarChar,500),
               new SqlParameter("@pageSize",SqlDbType.Int,4),
               new SqlParameter("@page",SqlDbType.Int,4),
               new SqlParameter("@pageCount",SqlDbType.Int,4),
               new SqlParameter("@Counts",SqlDbType.Int,4),
               new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
               new SqlParameter("@Sort",SqlDbType.Bit,1),
               new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
               new SqlParameter("@ID",SqlDbType.NVarChar,150),
               new SqlParameter("@Dist",SqlDbType.Bit,1)
            };
            parameters[0].Value = "Users";
            parameters[1].Value = "Id,Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,LastLoginOn";
            parameters[2].Value = size;
            parameters[3].Value = page;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            parameters[6].Value = "CreateOn";
            parameters[7].Value = true;
            parameters[8].Value = "IsActived=" + isLockedOut;
            parameters[9].Value = "Id";
            parameters[10].Value = false;
            #endregion
            SqlDataReader reader = DbHelper.RunProcedure("proc_QueryPage", parameters);
            User user = new User();
            while (reader.Read())
            {
                user.Id = reader.GetInt32(0);
                user.Name = reader.GetString(1);
                user.TrueName = reader.GetString(2);
                user.Gender = reader.GetInt16(3);
                user.Password = reader.GetString(4);
                user.Birthday = reader.GetDateTime(5);
                user.IsLockedOut = reader.GetBoolean(6);
                user.IsActived = reader.GetBoolean(7);
                user.CreateOn = reader.GetDateTime(8);
                user.LastLoginOn = reader.GetDateTime(9);
                result.DataList.Add(user);
            }
            result.Count = int.Parse(parameters[5].Value.ToString());
            result.PageCount = int.Parse(parameters[4].Value.ToString());
            return result;
        }
        /// <summary>
        /// 搜索用户
        /// </summary>
        /// <param name="type">搜索类型</param>
        /// <param name="keyword">关键字</param>
        /// <param name="page">当前页码</param>
        /// <param name="size">每页记录数</param>
        /// <returns></returns>
        public static QueryResult<User> SearchUsers(string type, string keyword, int page, int size)
        {
            QueryResult<User> result = new QueryResult<User>(page, size);
            #region 设置存储过程参数
            SqlParameter[] parameters = {
               new SqlParameter("@tblName",SqlDbType.NVarChar,200),
               new SqlParameter("@fldName",SqlDbType.NVarChar,500),
               new SqlParameter("@pageSize",SqlDbType.Int,4),
               new SqlParameter("@page",SqlDbType.Int,4),
               new SqlParameter("@pageCount",SqlDbType.Int,4),
               new SqlParameter("@Counts",SqlDbType.Int,4),
               new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
               new SqlParameter("@Sort",SqlDbType.Bit,1),
               new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
               new SqlParameter("@ID",SqlDbType.NVarChar,150),
               new SqlParameter("@Dist",SqlDbType.Bit,1)
            };
            parameters[0].Value = "Users";
            parameters[1].Value = "Id,Name,TrueName,Gender,Password,Birthday,IsLockedOut,IsActived,CreateOn,LastLoginOn";
            parameters[2].Value = size;
            parameters[3].Value = page;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            parameters[6].Value = "CreateOn";
            parameters[7].Value = true;
            parameters[8].Value = type+" like \'%" + keyword+"%\'";
            parameters[9].Value = "Id";
            parameters[10].Value = false;
            #endregion
            SqlDataReader reader = DbHelper.RunProcedure("proc_QueryPage", parameters);
            User user = new User();
            while (reader.Read())
            {
                user.Id = reader.GetInt32(0);
                user.Name = reader.GetString(1);
                user.TrueName = reader.GetString(2);
                user.Gender = reader.GetInt16(3);
                user.Password = reader.GetString(4);
                user.Birthday = reader.GetDateTime(5);
                user.IsLockedOut = reader.GetBoolean(6);
                user.IsActived = reader.GetBoolean(7);
                user.CreateOn = reader.GetDateTime(8);
                user.LastLoginOn = reader.GetDateTime(9);
                result.DataList.Add(user);
            }
            result.Count = int.Parse(parameters[5].Value.ToString());
            result.PageCount = int.Parse(parameters[4].Value.ToString());
            return result;

        }
        /// <summary>
        /// 删除一个用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static bool DeleteUser(User user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Users ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = user.Id;

            int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 移除用户的某个角色
        /// </summary>
        /// <param name="role">角色</param>
        /// <param name="user">用户</param>
        /// <returns>是否成功</returns>
        public static bool RemoveRoleFromUser(Role role, User user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UsersInRole");
            strSql.Append("where UserId=@userId and RoleId=@roldId");
            SqlParameter[] parameters = { 
                                            new SqlParameter("@userId",user.Id),
                                            new SqlParameter("@roleId",role.Id)
                                        };
            int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

    }
}
